/**
 * Author : Sam
 * Email: samlin.bear@gmail.com
 * copyright&copy; sam
 */
package com.linkwings.dbTemp;

import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.SessionFactoryUtils;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.linkwings.dbTemp.EntityDao;
import com.linkwings.dbTemp.DataBaseManager;
import com.linkwings.dbTemp.PageContainer;
import com.linkwings.dbTemp.ParameterCheckUtil;
import com.linkwings.dbTemp.ParameterException;

/**
 * @author LK
 */
@SuppressWarnings("unchecked")
public class HibernateEntityDaoImpl<T> extends HibernateDaoSupport implements
    EntityDao {
	
	protected Logger log = Logger.getLogger(this.getClass());
	protected Class<T> entityClass;
	
	public HibernateEntityDaoImpl(){
		this.entityClass=GenericsUtils.getSuperClassGenricType(this.getClass());
	}

	private static String changeCountHql(String hql){
		String lowerHql=hql.trim().toLowerCase();
		String currentHql=hql.trim();
		String str1=")";
		String str2="order";
		String str3="by";
		int s1=lowerHql.lastIndexOf(str1);
		int s2=lowerHql.lastIndexOf(str2);
		int s3=lowerHql.lastIndexOf(str3);
		//System.out.println("s1["+s1+"]");
		//System.out.println("s2["+s2+"]");
		//System.out.println("s3["+s3+"]");
		if(s2!=-1&&s3!=-1&&s3>s2){
			String str4=currentHql.substring(s2+5,s3);
			System.out.println("str4["+str4+"]");
			if(str4.length()>=1&&str4.trim().equals("")&&(s1==-1||s1<s2)){
				return currentHql.substring(0,s2);
			}
		}
		return currentHql;
	}	
	
	public int countDataSize(final String hql, final Object[] params){
		int count = 0;
		List list = (List) getHibernateTemplate().execute(new HibernateCallback() {
			
			@SuppressWarnings("unchecked")
			public Object doInHibernate(Session session) throws HibernateException,
			    SQLException {
				String countHql=changeCountHql("select count(*) from " + hql.substring(hql.toLowerCase().indexOf("from") + 4));
				Query query = session.createQuery(countHql);
				if ( params != null ) {
					for ( int i = 0; i < params.length; i++ ) {
						query.setParameter(i, params[i]);
					}
				}
				List list2 = query.list();
				return list2;
			}
		});
		
		if ( !list.isEmpty() ) {
			count = Integer.valueOf(list.get(0).toString()).intValue();
		}
		return count;
	}
	
	
	protected int countDataSize(HqlTool tool){
		StringBuffer hql = tool.getHql();
		List<Object> paramList = tool.getParamList();
		
		int count = this.countDataSize(hql.toString(), paramList.toArray());
		
		return count;
	}
	
	
	public int executeUpdate(final String query, final Object[] params){
		return getHibernateTemplate().bulkUpdate(query, params);
	}
	
	protected int executeUpdate(final HqlTool tool){
		StringBuffer hql = tool.getHql();
		List<Object> paramList = tool.getParamList();
		
		return getHibernateTemplate().bulkUpdate(hql.toString(), paramList.toArray());
	}
	
	@Deprecated
	public List executeQueryList(final String hql, final Object[] params){

		List list = (List) getHibernateTemplate().execute(new HibernateCallback() {
			
			@SuppressWarnings("unchecked")
			public Object doInHibernate(Session session) throws HibernateException,
			    SQLException {
				Query query = session.createQuery(hql);
				if ( params != null ) {
					for ( int i = 0; i < params.length; i++ ) {
						query.setParameter(i, params[i]);
					}
				}
				return query.list();
			}
		});
		return list;
	}
	
	
	public DataBaseManager executeQueryBySql(final String sql, final Object[] params){

		Session session = null;
		//ResultSet result = null;
		try {
			session = this.getHibernateTemplate().getSessionFactory().openSession();
			Connection con = SessionFactoryUtils.getDataSource(session.getSessionFactory()).getConnection();
			return this.executeQueryBySql(sql, params, con);
			
    } catch ( SQLException e ) {
	    e.printStackTrace();
	    return null;
    }
}
	
	
	public DataBaseManager executeBySql(final String sql, final Object[] params){

		Session session = null;
		boolean result = false;
		try {
			session = this.getHibernateTemplate().getSessionFactory().openSession();
			Connection con = SessionFactoryUtils.getDataSource(session.getSessionFactory()).getConnection();
			return this.executeBySql(sql, params, con);
    } catch ( SQLException e ) {
	    e.printStackTrace();
	    return null;
    }
}	
	public DataBaseManager executeQueryBySql(final String sql, final Object[] params,Connection con2){
		ResultSet result = null;
		try {
			//session = this.getHibernateTemplate().getSessionFactory().openSession();
			Connection con = con2;
			DataBaseManager dbm=new DataBaseManager();
			dbm.setConnection(con);
			PreparedStatement ps = null;
			ps = con.prepareStatement(sql.toString());
			
			if(params!=null){
			for(int i=0;i<params.length;i++){
				Object obj = params[i];
				ps.setObject(i+1, obj);
			}
			}
			result = ps.executeQuery();
			dbm.setResultSet(result);
			return dbm;
			
    } catch ( SQLException e ) {
	    e.printStackTrace();
	    return null;
    }		
	}	
	public DataBaseManager executeBySql(final String sql, final Object[] params,Connection con2){
		boolean result = false;
		try {
			//session = this.getHibernateTemplate().getSessionFactory().openSession();
			Connection con = con2;
			DataBaseManager dbm=new DataBaseManager();
			dbm.setConnection(con);
			PreparedStatement ps = null;
			ps = con.prepareStatement(sql.toString());
			
			if(params!=null){
			for(int i=0;i<params.length;i++){
				Object obj = params[i];
				ps.setObject(i+1, obj);
			}
			}
			result = ps.execute();
			dbm.setIsSuccess(result);
			return dbm;
			
    } catch ( SQLException e ) {
	    e.printStackTrace();
	    return null;
    }		
	}
	
	public List sqlFindDateListMap(final String sql,final Object[] params) throws Exception{
		DataBaseManager dm= this.executeQueryBySql(sql, params);
		List list= DbUtils.resultSetToListMap(dm.getResultSet());
		dm.close();
		return list;
	}
	
	public List sqlFindDateListObject(final String sql,final Object[] params,Class arg) throws Exception{
		DataBaseManager dm= this.executeQueryBySql(sql, params);
		List list= DbUtils.resultSetToListObject(dm.getResultSet(), arg);
		dm.close();
		return list;		
	}
	
	public List sqlFindDateListObject(final String sql,final Object[] params,Class arg,Map columnNameMap) throws Exception{
		DataBaseManager dm= this.executeQueryBySql(sql, params);
		List list= DbUtils.resultSetToListObject(dm.getResultSet(), arg,columnNameMap);
		dm.close();
		return list;		
	}
	
	public List sqlFindDateListMapperObject(final String sql,final Object[] params,Class arg) throws Exception{
		DataBaseManager dm= this.executeQueryBySql(sql, params);
		List list= DbUtils.resultSetToListMapperObject(dm.getResultSet(),arg);
		dm.close();
		return list;		
	}
	
	public List sqlFindDateListMapperObject(final String sql,final Object[] params,Class arg,String mapperFieldName) throws Exception{
		DataBaseManager dm= this.executeQueryBySql(sql, params);
		List list= DbUtils.resultSetToListMapperObject(dm.getResultSet(),arg,mapperFieldName);
		dm.close();
		return list;	
	}
	
	
		/*ResultSet rs = (ResultSet) getHibernateTemplate().execute(
		    new HibernateCallback() {
			    
			    @SuppressWarnings("unchecked")
			    public Object doInHibernate(Session session)
			        throws HibernateException, SQLException {
				    Connection con = SessionFactoryUtils.getDataSource(
				        session.getSessionFactory()).getConnection();
				    PreparedStatement pstmt = con.prepareStatement(sql);
				    if ( params != null && params.length > 0 ) {
					    for ( int i = 1; i <= params.length; i++ ) {
						    pstmt.setObject(i, params[i - 1]);
					    }
				    }
				    
				    return pstmt.executeQuery();
			    }
	 });*/
	
	
	
	public void deleteObject(Object vo){
		getHibernateTemplate().delete(vo);
	}
	
	
	public void deleteObjectByPk(Serializable pk) {
		Object obj = this.findObjectByPk(pk, this.entityClass);
		this.deleteObject(obj);
	}
	
	
	public void deleteObjectByPk(Serializable pk, Class arg){
		Object obj = this.findObjectByPk(pk, arg);
		this.deleteObject(obj);
	}
	
	
	public void deleteCollection(Collection dataList) {
		getHibernateTemplate().deleteAll(dataList);
	}
	
	
	public T findObjectByPk(Serializable id) {
		Object obj = getHibernateTemplate().get(this.entityClass, id);
		if ( obj == null ) {
			return null;
		} else {
			return (T) obj;
		}
	}
	
	
	public Object findObjectByPk(Serializable id, Class arg){
		Object obj = getHibernateTemplate().get(arg, id);
		if ( obj == null ) {
			return null;
		} else {
			return obj;
		}
	}
	
	
	public T findObjectByQuery(final String hql, final Object[] params){
		List list = (List) getHibernateTemplate().execute(new HibernateCallback() {
			
			@SuppressWarnings("unchecked")
			public Object doInHibernate(Session session) throws HibernateException,
			    SQLException {
				Query query = session.createQuery(hql);
				if ( params != null ) {
					for ( int i = 0; i < params.length; i++ ) {
						query.setParameter(i, params[i]);
					}
				}
				query.setFirstResult(0);// 开始数据的位置
				query.setMaxResults(1);
				return query.list();
			}
		});
		if ( list.size() > 0 ) {
			return (T) list.get(0);
		} else {
			return null;
		}
	}
	
	
	public List<T> findDataList(String hql, Object[] params){
		if ( params == null ) {
			return getHibernateTemplate().find(hql);
		} else {
			return getHibernateTemplate().find(hql, params);
		}
	}
	
	
	public List<T> findDataList(final String hql,final Object[] params, final Integer offset,
	    final Integer pageSize){

		//ParameterCheckUtil.checkOffsetAndPagesize(offset, pageSize);
		
		if(offset==null || pageSize==null){
			return this.findDataList(hql, params);
		}else{
		
			List list = (List) getHibernateTemplate().execute(new HibernateCallback() {
				
				@SuppressWarnings("unchecked")
				public Object doInHibernate(Session session) throws HibernateException,
				    SQLException {
					Query query = session.createQuery(hql);
					if ( params != null ) {
						for ( int i = 0; i < params.length; i++ ) {
							query.setParameter(i, params[i]);
						}
					}
					
					query.setFirstResult(offset);// 开始数据的位置
					query.setMaxResults(pageSize);
					return query.list();
				}
			});
			return list;
		}
	}
	
	
	
	protected List<T> findDataList(final HqlTool tool, final Integer offset,
	    final Integer pageSize){

		StringBuffer hql = tool.getHql();
		List<Object> paramList = tool.getParamList();
		
		List<T> list = this.findDataList(hql.toString(), paramList.toArray(), offset, pageSize);
		
		return list;
	}
	
	
	public PageContainer<T> findPageData(final String hql, final Object[] params,
	    final int offset, final int pageSize) throws ParameterException {
		ParameterCheckUtil.checkOffsetAndPagesize(offset, pageSize);
		PageContainer<T> pc = (PageContainer) getHibernateTemplate().execute(
		    new HibernateCallback() {
			    
			    @SuppressWarnings("unchecked")
			    public Object doInHibernate(Session session)
			        throws HibernateException, SQLException {
				    
				    PageContainer<T> container = new PageContainer<T>();
				    
				    
				    // 查询总数据大小
				    int dataSize = 0;
				    String countHql=changeCountHql("select count(*) from " + hql.substring(hql.toLowerCase().indexOf("from") + 4));
				    Query countQuery = session.createQuery(countHql);
				    if ( params != null ) {
					    for ( int i = 0; i < params.length; i++ ) {
						    countQuery.setParameter(i, params[i]);
					    }
				    }
				    List countList = countQuery.list();
				    if ( !countList.isEmpty() ) {
					    dataSize = Integer.valueOf(countList.get(0).toString())
					        .intValue();
				    }
				    
				    
				    // 处理参数
				    int pgoffset = offset;
				    int pgSize = pageSize;
				    int pageTotal = 1;// 总页数
				    if ( pgSize < 1 ) {
					    pgSize = 1;
				    }
				    container.setDataSize(dataSize);
				    container.setPageSize(pgSize);
				    
				    if ( dataSize == 0 ) {
					    pgoffset = 0;
					    container.setOffset(pgoffset);
					    container.setPageTotal(pageTotal);
					    container.setList(new ArrayList());
					    return container;
				    } else {
					    // 计算总页数
					    if ( dataSize % pgSize == 0 ) {
						    pageTotal = dataSize / pgSize;
					    } else {
						    pageTotal = dataSize / pgSize + 1;
					    }
					    
					    container.setOffset(pgoffset);
					    container.setPageTotal(pageTotal);
					    
					    
					    // 查询取出的数据
					    Query query = session.createQuery(hql);
					    if ( params != null ) {
						    for ( int i = 0; i < params.length; i++ ) {
							    query.setParameter(i, params[i]);
						    }
					    }
					    query.setFirstResult(pgoffset);// 开始数据的位置
					    query.setMaxResults(pageSize);
					    container.setList(query.list());
					    return container;
				    }
			    }
		    });
		
		//Edit by L.K
		//use to save the offset after use operate the list item		
		/*if(pc.getList().size()==0&&offset>0){
			int tempOffset=0;
			tempOffset=offset-pageSize;
			if(tempOffset<0){
				tempOffset=0;
			}
			pc=this.findPageData(hql, params, tempOffset, pageSize);
		}*/
		////
		
		return pc;
	}
	
	
	public PageContainer<T> findPageData(final String hql, final String counthql,
	    final Object[] params, final int offset, final int pageSize)
	    throws ParameterException {
		ParameterCheckUtil.checkOffsetAndPagesize(offset, pageSize);
		PageContainer<T> pc = (PageContainer) getHibernateTemplate().execute(
		    new HibernateCallback() {
			    
			    @SuppressWarnings("unchecked")
			    public Object doInHibernate(Session session)
			        throws HibernateException, SQLException {
				    
				    PageContainer<T> container = new PageContainer<T>();
				    
				    
				    // 查询总数据大小
				    int dataSize = 0;
				    Query countQuery = session.createQuery(counthql);
				    if ( params != null ) {
					    for ( int i = 0; i < params.length; i++ ) {
						    countQuery.setParameter(i, params[i]);
					    }
				    }
				    List countList = countQuery.list();
				    if ( !countList.isEmpty() ) {
					    dataSize = Integer.valueOf(countList.get(0).toString())
					        .intValue();
				    }
				    
				    
				    // 处理参数
				    int pgoffset = offset;
				    int pgSize = pageSize;
				    int pageTotal = 1;// 总页数
				    if ( pgSize < 1 ) {
					    pgSize = 1;
				    }
				    container.setDataSize(dataSize);
				    container.setPageSize(pgSize);
				    
				    if ( dataSize == 0 ) {
					    pgoffset = 0;
					    container.setOffset(pgoffset);
					    container.setPageTotal(pageTotal);
					    container.setList(new ArrayList());
					    return container;
				    } else {
					    // 计算总页数
					    if ( dataSize % pageSize == 0 ) {
						    pageTotal = dataSize / pgSize;
					    } else {
						    pageTotal = dataSize / pgSize + 1;
					    }
					    
					    container.setOffset(pgoffset);
					    container.setPageTotal(pageTotal);
					    
					    
					    // 查询取出的数据
					    Query query = session.createQuery(hql);
					    if ( params != null ) {
						    for ( int i = 0; i < params.length; i++ ) {
							    query.setParameter(i, params[i]);
						    }
					    }
					    query.setFirstResult(pgoffset);// 开始数据的位置
					    query.setMaxResults(pageSize);
					    container.setList(query.list());
					    return container;
				    }
			    }
		    });
		
		//Edit by L.K
		//use to save the offset after use operate the list item
		/*if(pc.getList().size()==0&&offset>0){
			int tempOffset=0;
			tempOffset=offset-pageSize;
			if(tempOffset<0){
				tempOffset=0;
			}
			pc=this.findPageData(hql,counthql, params, tempOffset, pageSize);
		}*/
		///
		
		return pc;
	}
	
	
	public PageContainer<T> findPageData(final String hql,final int count,final Object[] params,final int offset,final int pageSize)throws ParameterException {
		ParameterCheckUtil.checkOffsetAndPagesize(offset, pageSize);
		PageContainer<T> pc = (PageContainer) getHibernateTemplate().execute(
		    new HibernateCallback() {
			    
			    @SuppressWarnings("unchecked")
			    public Object doInHibernate(Session session)
			        throws HibernateException, SQLException {
				    
				    PageContainer<T> container = new PageContainer<T>();
				    
				    
				    // 总数据大小
				    int dataSize = count;				    
				    // 处理参数
				    int pgoffset = offset;
				    int pgSize = pageSize;
				    int pageTotal = 1;// 总页数
				    if ( pgSize < 1 ) {
					    pgSize = 1;
				    }
				    container.setDataSize(dataSize);
				    container.setPageSize(pgSize);
				    
				    if ( dataSize == 0 ) {
					    pgoffset = 0;
					    container.setOffset(pgoffset);
					    container.setPageTotal(pageTotal);
					    container.setList(new ArrayList());
					    return container;
				    } else {
					    // 计算总页数
					    if ( dataSize % pageSize == 0 ) {
						    pageTotal = dataSize / pgSize;
					    } else {
						    pageTotal = dataSize / pgSize + 1;
					    }
					    
					    container.setOffset(pgoffset);
					    container.setPageTotal(pageTotal);
					    
					    
					    // 查询取出的数据
					    Query query = session.createQuery(hql);
					    if ( params != null ) {
						    for ( int i = 0; i < params.length; i++ ) {
							    query.setParameter(i, params[i]);
						    }
					    }
					    query.setFirstResult(pgoffset);// 开始数据的位置
					    query.setMaxResults(pageSize);
					    container.setList(query.list());
					    return container;
				    }
			    }
		    });

		
		return pc;		
	}
	
	
	public PageContainer<T> findPageData_aotoset(final String hql,
	    final Object[] params, final int pageSize, HttpServletRequest request,
	    String[] excludeURLParams) throws ParameterException {
		ParameterCheckUtil.checkPlusInteger("pageSize", pageSize, false);
		
		int offset = 0;
		if ( request.getParameter("pager.offset") != null ) {
			try {
				offset = Integer.parseInt(request.getParameter("pager.offset")
				    .toString());
			} catch ( Exception r ) {
				offset = 0;
			}
		}
		PageContainer<T> container = findPageData(hql, params, offset, pageSize);
		container.setPageURI(request.getRequestURI());
		if ( excludeURLParams != null ) {
			List<String> excludeParamsList = new ArrayList<String>();
			excludeParamsList.add("pager.offset");
			for ( int i = 0; i < excludeURLParams.length; i++ ) {
				if ( excludeParamsList.contains(excludeURLParams[i]) == false ) {
					excludeParamsList.add(excludeURLParams[i]);
				}
			}
			
			List list = WebUtils.getURLParamsList(request,
			    (String[]) excludeParamsList.toArray());
			container.setParamlist(list);
		} else {
			List list = WebUtils.getURLParamsList(request,
			    new String[] {"pager.offset"});
			container.setParamlist(list);
		}
		return container;
		
	}
	
	
	public PageContainer<T> findPageData_aotoset(final String hql,
	    final String counthql, final Object[] params, final int pageSize,
	    HttpServletRequest request, String[] excludeURLParams)
	    throws ParameterException {
		ParameterCheckUtil.checkPlusInteger("pageSize", pageSize, false);		
		int offset = 0;
		if ( request.getParameter("pager.offset") != null ) {
			try {
				offset = Integer.parseInt(request.getParameter("pager.offset")
				    .toString());
			} catch ( Exception r ) {
				offset = 0;
			}
		}
		PageContainer<T> container = findPageData(hql, counthql, params, offset,
		    pageSize);
		container.setPageURI(request.getRequestURI());
		if ( excludeURLParams != null ) {
			List<String> excludeParamsList = new ArrayList<String>();
			excludeParamsList.add("pager.offset");
			for ( int i = 0; i < excludeURLParams.length; i++ ) {
				if ( excludeParamsList.contains(excludeURLParams[i]) == false ) {
					excludeParamsList.add(excludeURLParams[i]);
				}
			}
			
			List list = WebUtils.getURLParamsList(request,
			    (String[]) excludeParamsList.toArray());
			container.setParamlist(list);
		} else {
			List list = WebUtils.getURLParamsList(request,
			    new String[] {"pager.offset"});
			container.setParamlist(list);
		}
		return container;
	}
	
	
	public void saveOrUpdateCollection(Collection dataList)
	   {
		getHibernateTemplate().saveOrUpdateAll(dataList);
	}
	
	
	public void saveOrUpdate(Object vo){
		getHibernateTemplate().saveOrUpdate(vo);
	}
	
	
	public ResultSet callableExecute(final String callProc,
	    final Object[] params, final int[] outParamsTypes) {		
		ResultSet rs = (ResultSet) getHibernateTemplate().execute(
		    new HibernateCallback() {
			    
			    @SuppressWarnings("unchecked")
			    public Object doInHibernate(Session session)
			        throws HibernateException, SQLException {
				    Connection con = SessionFactoryUtils.getDataSource(
				        session.getSessionFactory()).getConnection();
				    CallableStatement cstmt = con.prepareCall(callProc);
				    if ( params != null && params.length > 0 ) {
					    for ( int i = 1; i <= params.length; i++ ) {
						    cstmt.setObject(i, params[i - 1]);
					    }
				    }
				    if ( outParamsTypes != null && outParamsTypes.length > 0 ) {
					    for ( int i = 1; i <= outParamsTypes.length; i++ ) {
						    cstmt.registerOutParameter(i, outParamsTypes[i - 1]);
					    }
				    }
				    return cstmt.executeQuery();
			    }
		    });
		return rs;
	}
	@Deprecated
	public List executeQueryList(final String hql,final Object[] params,final Integer offset,final Integer pageSize) {
			List list = (List) getHibernateTemplate().execute(new HibernateCallback() {
			
			@SuppressWarnings("unchecked")
			public Object doInHibernate(Session session) throws HibernateException,
			    SQLException {
				Query query = session.createQuery(hql);
				if ( params != null ) {
					for ( int i = 0; i < params.length; i++ ) {
						query.setParameter(i, params[i]);
					}
				}
				if(offset!=null && pageSize!=null){
					query.setFirstResult(offset);
					query.setMaxResults(pageSize);
				}
				return query.list();
			}
		});
		return list;
  }
}
